# par db module
'''
UNIT INFO
This module is the same as jodb.py, but it adds specification of
the database as a parameter in each function, whereas jodb.py only defines
the db at module-level. To access the default-db specified in db_species,
 use '' as database-name.
This module has replaced jodb.py in the long run.

UNIT INFO former jodb.py
This module exposes higher-level db-functions based on the dbapi-interface.
These functions are (or will be) db-generic. DB-specific functions are
implemented in module db_species (which will hold the db-interfaces of
'ms access thru pyodbc' and 'sqlite thru pysqlite2').
The database-functions (readFromParams, updateFromParams, addnewFromParams and
deleteFromParams) are the main functions of this module. They enable all the
sql-CRUD-operations (see below).
Because they ask for parameters, sql-building is isolated in this module.
(!! however there are still a few queries that are not yet paramatrized, and
use sql directly)


BACKGROUND INFO:
Based on the pyodbc / pysqlite modules which are based on dbapi2.
> cursor.exec en .fetch geeft terug een native object maar is niet echt een
tuple^2
> row is vergelijkbaar met tuple^1
>  .description contains: name, type_code, display_size, internal_size,
        precision, scale en null_ok
    (pysqlite implements only the field-name-part of the desciption,
    neccesatating a workaround)

---CRUD---
Create: INSERT INTO tab1 (col1,col2) VALUES (x,y)
Read: SELECT * FROM tab1 WHERE col1 = y
Update: UPDATE tab1 SET col1 = a, col2 = b WHERE  col0= x
Delete: DELETE FROM tab1 WHERE col1 = x
------------

Data-type input:
Which type-systems exist and when and how are they converted?
    -type-systems:
        -ms access
        - sqlite
        -python
        -odbc
        -dbapi2
Types:
-msnumber/float can also be in string-format (?then it is applying
the literal string or the PC-language-setting?).
-select / bool > selected (use legal field-name)
-msdate
    -format like string like it is read '2008-06-07 15:39:28'
Null-values:
    -date and numbers cannot be set to empty string; use NULL
    -bool must false or true, not NULL

ADAP HIS
-adapted all
-test callbacks:
    -getDataTypeList and getDataTypeDict
        -call back to pardb.py

ADAP NO


ADAP FUT
-speed-up
    - check if tuple can be used instead of list
    - use fetchall?
-implement negation in compareclause
    -problem: null-values excluded from negation-query
        -mid-term solution:
            -create an extra stored query
        -long-term solution:
            -create a new list-based function with:
                -[datatypeplus, fieldname, comparison operator,
                value1, value2]
                -boolean template: a and not b or c (%s and not %s or %s)
-abolish the 'strings' and 'numbers' dicts by inspecting the datatype from the
database



'''


import db_species
import jotools
from datetime import datetime


def countFromCursor(cursor):
    # does not  work; gives -1; use for now rowCountFromParams
    return cursor.rowcount

def sqlFromParams(tablest, fieldli=None, criteriadi2=None):
    # read-only
    if fieldli == None:
        fieldst = '*'
    else:
        fieldst = ','.join(fieldli)

    sqlst = 'SELECT ' + fieldst + ' FROM ' + tablest + whereClause(criteriadi2)
##    print sqlst     # enable print sql
    return sqlst


def whereClause(criteriadi2):
    if criteriadi2 == None:
        whereClausest = ''
    else:
        whereClausest =  " WHERE " + compareClause(criteriadi2,'where')

    return whereClausest

def setClause(criteriadi2):
    if criteriadi2 == None:
        setClausest = ''
    else:
        setClausest =  " SET " + compareClause(criteriadi2,'set')

    return setClausest

def compareClause(criteriadi2,usagetypest):
    '''
    UNIT INFO
    This function converts a criteria-dict to the relevant sql-statements for
    both select-filter and criteria for update and add. However these
    sql-statements may diverge on some rare instances, eg like
    in Null-handling.

    Example-call-criteria:
    {'strings':{'name':'hendrik','address':'road 1'},'numbers':{'age': 16},
    'subs:{'name':'aa'},'nullslist':['address','town']}

    Current types:
        -strings, subs (substrings), beginwith (stringtype), numbers,
        stringsnot (not this string), nullslist (uses fieldlist - must be null)
        -bools must use numbers with 0 and 1

    Usagetype is 'set' or 'where' (determines concatenator)

    ADAP NO
    ADAP FUT:
        -add all datatypes
        -datetime-usage is not clear
    '''

    ds = db_species.dateSeparator()

    if usagetypest == 'where':
        concatenatorst = ' AND '
    elif usagetypest == 'set':
        concatenatorst = ', '

    compareClausest = ''
    if criteriadi2 <> None:
        dtypeli = criteriadi2.keys()
##        print dtypeli
        if 'strings' in dtypeli:
            strCridi = criteriadi2['strings']
            strComparest = concatenatorst.join(["%s='%s'" % (k,v) for k,v in strCridi.items()])
            compareClausest = strComparest
        if 'stringsnot' in dtypeli: # not this string
            snoCridi = criteriadi2['stringsnot']
            snoComparest = concatenatorst.join(["NOT %s='%s'" % (k,v) for k,v in snoCridi.items()])
            if compareClausest == '':
                    compareClausest = snoComparest
            else:
                compareClausest = compareClausest + concatenatorst + snoComparest
        if 'str_bigger' in dtypeli:
            strCridi = criteriadi2['str_bigger']
            strComparest = concatenatorst.join(["%s>'%s'" % (k,v) for k,v in strCridi.items()])
            compareClausest = strComparest
        if 'str_smaller' in dtypeli:
            strCridi = criteriadi2['str_smaller']
            strComparest = concatenatorst.join(["%s<'%s'" % (k,v) for k,v in strCridi.items()])
            compareClausest = strComparest

        if 'subs' in dtypeli:   # substrings
            subCridi = criteriadi2['subs']
            subComparest = concatenatorst.join(["%s LIKE '%%%s%%'" % (k,v) for k,v in subCridi.items()])
            if compareClausest == '':
                compareClausest = subComparest
            else:
                compareClausest = compareClausest + concatenatorst + subComparest
        if 'subsnot' in dtypeli:   # not this substrings
            subCridi = criteriadi2['subsnot']
            subComparest = concatenatorst.join(["NOT %s LIKE '%%%s%%'" % (k,v) for k,v in subCridi.items()])
            if compareClausest == '':
                compareClausest = subComparest
            else:
                compareClausest = compareClausest + concatenatorst + subComparest
                
        if 'beginwith' in dtypeli:   # start with substring
            subCridi = criteriadi2['beginwith']
            subComparest = concatenatorst.join(["%s LIKE '%s%%'" % (k,v) for k,v in subCridi.items()])
            if compareClausest == '':
                compareClausest = subComparest
            else:
                compareClausest = compareClausest + concatenatorst + subComparest
        if 'endwith' in dtypeli:   # end with substring
            subCridi = criteriadi2['endwith']
            subComparest = concatenatorst.join(["%s LIKE '%%%s'" % (k,v) for k,v in subCridi.items()])
            if compareClausest == '':
                compareClausest = subComparest
            else:
                compareClausest = compareClausest + concatenatorst + subComparest

        if 'numbers' in dtypeli:
            numCridi = criteriadi2['numbers']
            numComparest = concatenatorst.join(["%s=%s" % (k,v) for k,v in numCridi.items()])
            if compareClausest == '':
                compareClausest = numComparest
            else:
                compareClausest = compareClausest + concatenatorst + numComparest
        if 'num_bigger' in dtypeli:
            numCridi = criteriadi2['num_bigger']
            numComparest = concatenatorst.join(["%s>%s" % (k,v) for k,v in numCridi.items()])
            if compareClausest == '':
                compareClausest = numComparest
            else:
                compareClausest = compareClausest + concatenatorst + numComparest
        if 'num_smaller' in dtypeli:
            numCridi = criteriadi2['num_smaller']
            numComparest = concatenatorst.join(["%s<%s" % (k,v) for k,v in numCridi.items()])
            if compareClausest == '':
                compareClausest = numComparest
            else:
                compareClausest = compareClausest + concatenatorst + numComparest

        if 'datetimes' in dtypeli:      # unused in sqlite
            datCridi = criteriadi2['datetimes']
##            datComparest = concatenatorst.join(["%s=#%s#" % (k,v) for k,v in datCridi.items()])
            stringst = "%s=" + ds + "%s" + ds
            datComparest = concatenatorst.join([stringst % (k,v) for k,v in datCridi.items()])
            if compareClausest == '':
                compareClausest = datComparest
            else:
                compareClausest = compareClausest + concatenatorst + datComparest
        if 'datebigger' in dtypeli:     # testing - unused in sqlite
            datCridi = criteriadi2['datebigger']
##            datComparest = concatenatorst.join(["%s=#%s#" % (k,v) for k,v in datCridi.items()])
            stringst = "%s>" + ds + "%s" + ds
            datComparest = concatenatorst.join([stringst % (k,v) for k,v in datCridi.items()])
            if compareClausest == '':
                compareClausest = datComparest
            else:
                compareClausest = compareClausest + concatenatorst + datComparest
                
        if 'nullslist' in dtypeli:
            nullsCrili = criteriadi2['nullslist']
            if usagetypest == 'set':
                nullsComparest = concatenatorst.join(["%s=NULL" % (k,) for k in nullsCrili])
            elif usagetypest == 'where':
                nullsComparest = concatenatorst.join(["%s ISNULL" % (k,) for k in nullsCrili])
            if compareClausest == '':
                compareClausest = nullsComparest
            else:
                compareClausest = compareClausest + concatenatorst + nullsComparest
        if 'nullsnot' in dtypeli:
            nullsCrili = criteriadi2['nullsnot']
            if usagetypest == 'set':
                nullsComparest = concatenatorst.join(["NOT %s=NULL" % (k,) for k in nullsCrili])
            elif usagetypest == 'where':
                nullsComparest = concatenatorst.join(["NOT %s ISNULL" % (k,) for k in nullsCrili])
            if compareClausest == '':
                compareClausest = nullsComparest
            else:
                compareClausest = compareClausest + concatenatorst + nullsComparest


    return compareClausest


def cursorFromSql(dbst, sqlst, writebo=False):
    cur = db_species.cursorFromDB(sqlst, writebo, dbst)
    return cur


def listFromCursor(cursor):
##    curli = list(cursor)
    curli = tuple(cursor)
    cursor.close()
    return curli

def writeFromSql(dbst, sqlst):
    # execute an update- or insert-statement to DB
    cur = cursorFromSql(dbst, sqlst, True)

def addnewFromParams(dbst, tablest, fieldvaluedi):
    '''
    Call like: addnewFromParams('',"Relations",{"Object1":'Nieuwtje',"MsNumber":50000})
    sql-output:    "INSERT INTO Relations (Object1,Relation) VALUES ('heelnieuw','relo')"

    ADAP FUT
    -improve the string for single-fielded addnews to allow
    non-string datatypes.
    '''

    fieldli = fieldvaluedi.keys()
    valueli = fieldvaluedi.values()
    fieldsetst = ','.join(fieldli)
    if len(valueli) > 1:    # 1-dimensional tuple can not exist
        sqlst = "INSERT INTO " + tablest + " (" +  fieldsetst + \
             ") VALUES " + str(tuple(valueli))
    else:
        sqlst = "INSERT INTO " + tablest + " (" +  fieldsetst + \
             ") VALUES ('" + str(valueli[0]) + "')"

##    print sqlst
    cur = cursorFromSql(dbst, sqlst,True)
    cur.close()


def addnewFromParamsWithNewID(dbst, tablest, fieldvaluedi):
    '''
    Call like: addnewFromParams('',"Relations",{"Object1":'Nieuwtje',"MsNumber":50000})
    sql-output:    "INSERT INTO Relations (Object1,Relation) VALUES ('heelnieuw','relo')"

    ADAP FUT
    -improve the string for single-fielded addnews to allow
    non-string datatypes.
    '''

    fieldli = fieldvaluedi.keys()
    valueli = fieldvaluedi.values()
    fieldsetst = ','.join(fieldli)
    if len(valueli) > 1:    # 1-dimensional tuple can not exist
        sqlst = "INSERT INTO " + tablest + " (" +  fieldsetst + \
             ") VALUES " + str(tuple(valueli))
    else:
        sqlst = "INSERT INTO " + tablest + " (" +  fieldsetst + \
             ") VALUES ('" + str(valueli[0]) + "')"

    print sqlst
    cur = cursorFromSql(dbst, sqlst,True)
    mylastrowid = cur.lastrowid
    cur.close()
    return mylastrowid


def updateFromParams(dbst, tablest, setcritdi2, wherecritdi2):
    '''
    Call like: updateFromParams('Relations',
        {'strings':{'Relation':'dropjes'},'numbers':{'age': 16}},
        {'strings':{'Object1':'neuig'}})
    Sql-output  "UPDATE Relations SET Object1 = 'handen wassen' WHERE Object1 = 'borden wassen'")
    '''
    sqlst = "UPDATE " + tablest + setClause(setcritdi2) + whereClause(wherecritdi2)

##    print sqlst
    cur = cursorFromSql(dbst, sqlst,True)
    cur.close()


def deleteFromParams(dbst, tablest, wherecritdi2):
    #   DELETE FROM tab1 WHERE col1 = x
    sqlst = "DELETE FROM " + tablest + whereClause(wherecritdi2)
    print sqlst
    cur = cursorFromSql(dbst, sqlst,True)
    cur.close()

def deleteAllFromTable(dbst, tablest):
    '''
    UNIT INFO
    Delete ALL records from the table.
    '''
    sqlst = "DELETE FROM " + tablest
    print sqlst
    cur = cursorFromSql(dbst, sqlst,True)
    cur.close()


def rowCountFromParams(dbst, tablest, fieldli=None, criteriadi2=None):
    '''
    UNIT INFO
    -call like: rowCountFromParams('Relations', ['Object1','Relation'],
        {'strings':{'Object1':'computer'}})
    ADAP FUT
    -use somehow the native rowcount
    '''

    cursor = cursorFromSql(dbst, sqlFromParams(
            tablest, fieldli, criteriadi2))
    rows = cursor.fetchall()
    it = 0
    for row in rows:
        it = it + 1
    cursor.close()
    return it

def readFromParams(dbst, tablest, fieldli=None, criteriadi2=None):
    '''
    UNIT INFO
    -call like: readFromParams('Relations', ['Object1','Relation'],
        {'strings':{'Object1':'computer'}})
    -returns a cursor-object which can be handled as list
    -append: .[0][0] to obtain a single value
    '''
    curli = listFromCursor(cursorFromSql(dbst, sqlFromParams(
            tablest, fieldli, criteriadi2)))
    return curli

def readDictFromParams(dbst, tablest, fieldli=None, criteriadi2=None):
    '''
    UNIT INFO
    -returns dictionary for the first row of the cursor (normally used
    for a single row)
    -call like: readDictFromParams('Relations', ['Object1','Relation'],
        {'strings':{'Object1':'computer'}})
    '''

    curli = listFromCursor(cursorFromSql(dbst, sqlFromParams(
            tablest, fieldli, criteriadi2)))
    fieldli = getFieldList(dbst, tablest)
    rowdi = {}
    n = 0
    for fieldst in fieldli:
        rowdi[fieldst] = curli[0][n]
        n = n + 1

    return rowdi


def testReadFromParams(dbst, tablest, fieldli=None, criteriadi2=None):
    '''
    UNIT INFO
    -call like: readFromParams('Relations', ['Object1','Relation'],
        {'strings':{'Object1':'computer'}})
    -returns a cursor-object which can be handled as list
    -append: .[0][0] to obtain a single value
    '''
    curli = cursorFromSql(dbst, sqlFromParams(tablest, fieldli, criteriadi2))
    printCursor(curli)
    return curli


def readFromSql(dbst, sqlst):
    curli = listFromCursor(cursorFromSql(dbst, sqlst))
    return curli

def readGroupsFromParams(dbst, tablest, fieldst):
    '''
    UNIT INFO
    Retrieve a column of data that is grouped and sorted on that field
    '''

    sqlst = "SELECT " + fieldst +  " FROM " + tablest + \
        " GROUP BY " + fieldst + " ORDER BY Lower(" + fieldst + ")"
    curli = listFromCursor(cursorFromSql(dbst, sqlst))
    return curli



def getFieldList(dbst, tablest):
    cursor = cursorFromSql(dbst, sqlFromParams(tablest))
    descriptu2 = cursor.description
    fieldNameli = [n for n,t,d,i,p,s,u in descriptu2]
    cursor.close()
    return fieldNameli

def getDataTypeList(dbst, tablest):
    return db_species.getDataTypeListDB(tablest, dbst)

def getDataTypeDict(dbst, tablest):
    return db_species.getDataTypeDictDB(tablest, dbst)

def mergeCritDicts(firstdi={},  seconddi={}):
    '''
    UNIT INFO:
    Merge two criteria-dicts, with underlying dicts and lists
    -uses special process for nullslist
    '''
    
    # create a thirddi
    thirddi = {}
    # for every item in the firstdi
    for thisitem in firstdi.items():
    # if the key exists in the seconddi
        if thisitem[0] in seconddi.keys():
            typetest = str(type(thisitem[1]))
            print typetest
            # merging based on object-type
            if typetest == "<type 'dict'>":
                # merge the dicts of the same key
                thisitem[1].update(seconddi[thisitem[0]])
            elif typetest == "<type 'list'>":
                # merge lists
                thisitem[1].extend(seconddi[thisitem[0]])
            # add to new dict
            thirddi[thisitem[0]] = thisitem[1]
    
        # else take only the item of the first key
        else:
            thirddi[thisitem[0]]=thisitem[1]

    # add the items of seconddi which are not in the firstdi
    for otheritem in seconddi.items():
        if not (otheritem[0] in firstdi.keys()):
            thirddi[otheritem[0]]=otheritem[1]
    return thirddi
    
    

def printCursor(cursor):
    rowcountit = 0
##    showfieldli = [0, 1, 2, 3, 4, 11]
    showfieldli = [0, 11]

    for row in cursor:
        line = ''
        it = 0
        rowcountit = rowcountit + 1
        for val in row:
            if it in showfieldli:
                line = line + ' - ' + str(val)
            it = it + 1
        print line
    print 'rowcount = ' + str( rowcountit)
##    cursor.close()


if __name__ == '__main__':
    '''
    ADAP NO
    '''
    
##    printCursor(readFromParams('', 'Relations', None,
##        {'nullsnot':['Subject']}))

##    printCursor(readFromParams('', 'Relations', None,
##        {'datebigger':{'MsDate':'2008-12-14'}}))

    printCursor(readFromParams('', 'Relations', None,
        {'str_bigger':{'MsDate':'2011-12-14'}}))

    
### ================
##    valvar = 25
##    fldvar = 'a'
##    fdi = {fldvar:{'x':valvar, 'y':2}, 'b':{'r':'korthaar'}, 'e':[1, 2]}
###    sdi = {'a':{'m':5, 'n':6}, 'c':{'s':'langhaar'}, 'e':[3, 4]}
##    sdi = {}
##    tdi = mergeCritDicts(fdi, sdi)
##    print tdi
###    fdi.update(sdi)
###    print fdi
### =================
    
##    dbs = db_species.curDatabase
##    curli  = readGroupsFromParams('','Relations','Object2')
##    printCursor(curli)


##    sqlst = "SELECT * FROM Relations WHERE Object2 LIKE 'huis%'"
##    printCursor(readFromSql(dbs, sqlst))


##    printCursor( readFromParams(dbs, 'Relations', None,
##        {'beginwith':{'Source':'jor'}}))

##    print readDictFromParams(dbs, 'RELATIONS', None,
##            {'numbers':{'RelID':3080}})['Object2']

##    testReadFromParams(dbs, 'History',None,
##        {'datetimes':{'Time':'2008-6-20 13:04:27'}})

##    print(readFromSql(
##        'select * from History where Time = #2008-6-20 13:04:27#'))

##    print(readFromSql(dbs, 'select * from History'))


##    print rowCountFromParams(dbs, 'Relations')

##    critrightdi = "Object1 = 'Form Relations View'"  + \
##        " AND (Change <> 'h' OR Change IS NULL)"
##    curli = readFromSql('SELECT * FROM Relations WHERE '+ critrightdi)
##    printCursor(curli)

##    xli =  readFromParams('Relations',['Object1'], {'numbers':{'RelID':4}})
##    print xli

##    updateFromParams(dbs,'Relations', {'numbers':{'Selected':1}, \
##        'strings':{'Object2':'neuf'}},
##        {'strings':{'Object1':'Dummy','Subject':'Algemeen'}})
##
##    sqlst = "UPDATE Relations SET Selected = 1 WHERE Object1 = 'kindB'"
##    writeFromSql(dbs, sqlst)

##    dick = {'strings':{'Relation':'dropjes','a':'bc'},'numbers':{'age': 16},\
##        'nullslist':['name','address']}
##    string = compareClause(dick,'set')
##    print string

##    print getDataTypeDict('Relations')

##    print getFieldList(dbs,'Relations')

##    printCursor(readFromSql(dbs, "SELECT * FROM Relations WHERE Object1 = 'handen wassen'"))

##    print addnewFromParamsWithNewID(dbs, 'Relations',{"Object1":'los',"Object2":'deze test',"MsNumber":111})

##    printCursor(readFromSql(dbs, 'SELECT last_insert_rowid()'))


##    updateFromParams(dbs, 'Relations', {'strings':{'Relation':'dropjes'}},
##        {'strings':{'Object2':'kindB'}})
##    deleteFromParams(dbs,'Relations', {'numbers':{'RelID':3928}})


##    printCursor( readFromParams(dbs, 'Relations', ['Object1','Relation'],
##        {'strings':{'Object1':'computer'}}))

##    sqlst = "UPDATE Relations SET Object2 = 'Kind A'" \
##         + " WHERE Object2 = 'kindCC'"
##    print sqlst
##    writeFromSql(dbs, sqlst)

##li = readFromParams(dbs, 'FDQ_SOURCES_GROUPED',['Source'])
##print type(li)
##print type(li[2])
##printCursor(li)


##    sqlst = "INSERT INTO Relations (MsLongText,Remark,Source,ModDate,MsDate, \
##        Object1,Object2,Relation,Selected,MsNumber,Subject,Change,MsCurrency) \
##        VALUES ('', '', '', '2004-12-01 00:00:00', NULL, 'bestand-deling-software', \
##        'poiu', 'asdf', False, NULL, 'Economie\\produktie', '', NULL)"


##    printCursor(readFromSql(dbs, "SELECT * FROM FDQ_RELATIONS_FLAT_FILTER WHERE Subject ISNULL"))

# ====================================
##    print 'hoi'
##    rowli = readDictFromParams(dbs, 'Relations', None, {'numbers':{'RelID':6}})
####    print rowli
##    longtekst = str(rowli['MsLongText'])
##    print longtekst
##
##    updateFromParams(dbs,'Relations', {'strings':{'MsLongText':longtekst}},
##        {'numbers':{'RelID':12}})
# =====================================


